package com.vegas.netspider.memdb.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.util.StringUtils;

import com.vegas.netspider.betdefine.SportBet;
import com.vegas.netspider.db.SportMatchDbHandler;
import com.vegas.netspider.dto.SportMatch;

public class SportMatchDao extends MemdbDao{

	private String tableName;
	private Map<Integer,List<SportBet>> cacheBetMap;
	private Map<Integer,List<SportBet>> cacheMoreMap;
	
	public SportMatchDao(String tb){
		this.tableName=tb;
		cacheBetMap = new HashMap<Integer, List<SportBet>>();
		cacheMoreMap = new HashMap<Integer, List<SportBet>>();
		initSql();
	}
	
	private String insertSql;
	private String removeSql;
	private String updateSql;
	private String queryByIdSql;
	private String queryByLeagueNamesSql;
	private String queryAll;
	private String deleteSql;
	private String existsIdSql;
	private void initSql(){
		insertSql="insert into "+tableName+"(match_id,league_name,home_team_name,home_team_id,guest_team_name,guest_team_id,play_time,will_play,running_time,current_ht_score,current_gt_score,sort) " +
				"values(?,?,?,?,?,?,?,?,?,?,?,?)";
		removeSql="delete from "+tableName+" where match_id=?";
		updateSql="update "+tableName+" set play_time=?,will_play=?,running_time=?,current_ht_score=?,current_gt_score=?,sort=? where match_id=?";
		queryByIdSql="select match_id,league_name,home_team_name,home_team_id,guest_team_name,guest_team_id,play_time,will_play,running_time,current_ht_score,current_gt_score,sort from "+tableName+" where match_id in(?)";
		queryByLeagueNamesSql="select match_id,league_name,home_team_name,home_team_id,guest_team_name,guest_team_id,play_time,will_play,running_time,current_ht_score,current_gt_score,sort from "+tableName+" where league_name in(?)";
		deleteSql="delete from "+tableName;
		existsIdSql="select match_id from "+tableName+"  where match_id=?";
		queryAll = "select match_id,league_name,home_team_name,home_team_id,guest_team_name,guest_team_id,play_time,will_play,running_time,current_ht_score,current_gt_score,sort from "+tableName;
	}
	
	private void cacheIn(Integer id,List<SportBet> list){
		cacheBetMap.put(id, list);
	}
	private List<SportBet> cacheOut(Integer id){
		return cacheBetMap.remove(id);
	}
	
	private void cacheMoreIn(Integer id,List<SportBet> list){
		cacheMoreMap.put(id, list);
	}
	private List<SportBet> cacheMoreOut(Integer id){
		return cacheMoreMap.remove(id);
	}

	
	
	public void insert(final SportMatch m){
		boolean exists = this.existsById(m.getMatchId());
		if(exists){
			this.update(m);
			return;
		}
		
		dao.update(insertSql,new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement p) throws SQLException {
				p.setInt(1, m.getMatchId());
				p.setString(2, m.getLeagueName());
				p.setString(3, m.getHomeTeamName());
				p.setInt(4, m.getHomeTeamId());
				p.setString(5, m.getGuestTeamName());
				p.setInt(6, m.getGuestTeamId());
				p.setLong(7, m.getPlayTime());
				p.setBoolean(8, m.isWillPlay());
				p.setString(9, m.getRunningTime());
				p.setInt(10, m.getCurrentHtScore()==null?0:m.getCurrentHtScore());
				p.setInt(11, m.getCurrentGtScore()==null?0:m.getCurrentGtScore());
				p.setInt(12, m.getSort());
			}
		});
		
		this.cacheIn(m.getMatchId(), m.getBets());
		if(m.getMore()!=null){
			this.cacheMoreIn(m.getMatchId(), m.getMore());
		}else{
			this.cacheMoreOut(m.getMatchId());
		}
		super.onInsert(tableName, m);
	}
	
	public void remove(Integer matchId){
		dao.update(removeSql, matchId);
		this.cacheOut(matchId);
		this.cacheMoreOut(matchId);
	}
	
	public boolean existsById(Integer matchId){
		//int result = dao.queryForInt(existsIdSql,matchId);
		int result = 0;
		List<Integer> list = dao.query(existsIdSql, new Object[]{matchId},new RowMapper<Integer>(){
			@Override
			public Integer mapRow(ResultSet r, int arg1) throws SQLException {
				return r.getInt("match_id");
			}
		});
		if(list!=null&&!list.isEmpty()){
			result = list.get(0);
		}
		if(result==0){
			return false;
		}
		
		return true;
	}
	
	public void update(final SportMatch m){
		boolean exists = this.existsById(m.getMatchId());
		if(!exists){
			return;
		}
		
		dao.update(updateSql, new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement p) throws SQLException {
				p.setLong(1, m.getPlayTime());
				p.setBoolean(2, m.isWillPlay());
				p.setString(3, m.getRunningTime());
				p.setInt(4, m.getMatchId());
				p.setInt(5, m.getCurrentHtScore()==null?0:m.getCurrentHtScore());
				p.setInt(6, m.getCurrentGtScore()==null?0:m.getCurrentGtScore());
				p.setInt(7, m.getSort());
			}
		});
		this.cacheIn(m.getMatchId(), m.getBets());
		if(m.getMore()!=null){
			this.cacheMoreIn(m.getMatchId(), m.getMore());
		}else{
			this.cacheMoreOut(m.getMatchId());
		}
		
	}
	
	
	private RowMapper<SportMatch> sportMatchRowMapper =	new RowMapper<SportMatch>(){
		@Override
		public SportMatch mapRow(ResultSet r, int arg1)
				throws SQLException {
			SportMatch sm = new SportMatch();
			sm.setMatchId(r.getInt("match_id"));
			sm.setLeagueName(r.getString("league_name"));
			sm.setHomeTeamName(r.getString("home_team_name"));
			sm.setGuestTeamName(r.getString("guest_team_name"));
			sm.setPlayTime(r.getLong("play_time"));
			sm.setWillPlay(r.getBoolean("will_play"));
			sm.setRunningTime(r.getString("running_time"));
			sm.setBets(cacheBetMap.get(sm.getMatchId()));
			sm.setMore(cacheMoreMap.get(sm.getMatchId()));
			sm.setCurrentHtScore(r.getInt("current_ht_score"));
			sm.setCurrentHtScore(r.getInt("current_gt_score"));
			sm.setHomeTeamId(r.getInt("home_team_id"));
			sm.setGuestTeamId(r.getInt("guest_team_id"));
			sm.setSort(r.getInt("sort"));
			return sm;
		}
	};
	
	
	public List<SportMatch> query(List<Integer> ids){
		String idsStr = StringUtils.arrayToDelimitedString(ids.toArray(), ",");
		return dao.query(queryByIdSql,new Object[]{idsStr},sportMatchRowMapper);
	}
	
	public List<SportMatch> query(){
		return dao.query(queryAll, sportMatchRowMapper);
	}
	
	public List<SportMatch> query(int index,int size){
		return dao.query(queryAll+" limit "+index+","+size, sportMatchRowMapper);
	}
	
	public List<SportMatch> query(String leagueName){
		return dao.query(queryByLeagueNamesSql,new Object[]{leagueName},sportMatchRowMapper);
	}
	
	
	
	@Override
	public void onClose() {
		this.cacheBetMap.clear();
		this.cacheMoreMap.clear();
		dao.update(deleteSql);
	}

	public String getTableName() {
		return tableName;
	}
	
	public JdbcTemplate getDao(){
		return dao;
	}
	
	public List<Integer> listExistMoreBetMatchId(){
		List<Integer> list = new ArrayList<Integer>(cacheMoreMap.keySet());
		return list;
	}
	public void updateMoreBet(SportMatch sm){
		if(sm==null||sm.getMatchId()==null){
			return;
		}
		
		if(sm.getBets()==null||sm.getBets().isEmpty()){
			this.cacheMoreOut(sm.getMatchId());
			return;
		}
		
		this.cacheMoreIn(sm.getMatchId(), sm.getBets());
		
	}
	
}
